package com.study.dao.impl;

import com.study.dao.SportingEventsDAO;
import com.study.pojo.SportingEvents;
import com.study.util.JdbcUtil;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * @PackgeName: com.study.dao.impl
 * @ClassName: SportingEventsDAOImpl
 * @Author: 严志祥
 * Date: 2022-05-04 19:44
 * project name: web_project_medal
 * @Version:
 * @Description:
 */
public class SportingEventsDAOImpl implements SportingEventsDAO {
	private final static Logger log = LoggerFactory.getLogger(SportingEventsDAOImpl.class);
    Connection conn = JdbcUtil.getConn();
    @Override
    public int insert(SportingEvents entity) throws SQLException {
        String sql = "insert into sporting_events (sporting_name,sporting_brief) values (?,?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,entity.getSportingName());
        pstmt.setObject(2,entity.getSportingBrief());
        log.debug(sql);
        return pstmt.executeUpdate();
    }

    @Override
    public int updateById(SportingEvents entity) throws SQLException {
        String sql = "update sporting_events set sporting_name=?,sporting_brief=? where sporting_id = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,entity.getSportingName());
        pstmt.setObject(2,entity.getSportingBrief());
        pstmt.setObject(3,entity.getSportingId());
        log.debug(sql);
        return pstmt.executeUpdate();
    }

    @Override
    public int deleteById(Serializable id) throws SQLException {
        String sql = "delete from sporting_events where sporting_id = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,id);
        log.debug(sql);
        return pstmt.executeUpdate();
    }

    @Override
    public SportingEvents selectById(Serializable id) throws SQLException {
        String sql = "select * from sporting_events where sporting_id = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,id);
        ResultSet rs = pstmt.executeQuery();
        SportingEvents sportingEvents = new SportingEvents();
        if(rs.next()){
            sportingEvents.setSportingId(rs.getInt("sporting_id"));
            sportingEvents.setSportingName(rs.getString("sporting_name"));
            sportingEvents.setSportingBrief(rs.getString("sporting_brief"));
        }
        log.debug(sql);
        return sportingEvents;
    }

    @Override
    public List<SportingEvents> select(int index, int len) throws SQLException {
        String sql = "select * from sporting_events order by sporting_id desc limit ?,?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,index);
        pstmt.setObject(2,len);
        ResultSet rs = pstmt.executeQuery();
        List<SportingEvents> list = new ArrayList<>();
        while(rs.next()){
            SportingEvents sportingEvents = new SportingEvents();
            sportingEvents.setSportingId(rs.getInt("sporting_id"));
            sportingEvents.setSportingName(rs.getString("sporting_name"));
            sportingEvents.setSportingBrief(rs.getString("sporting_brief"));
            list.add(sportingEvents);
        }
        log.debug(sql);
        return list;
    }

    @Override
    public long count() throws SQLException {
        String sql = "select count(*) from sporting_info";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        rs.next();
        int rsInt = rs.getInt("count(*)");
        log.debug(sql);
        return rsInt;
    }

    @Override
    public boolean selectSporting(String sportingName) throws SQLException {
        String sql = "select * from sporting_events where sporting_name = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,sportingName);
        ResultSet rs = pstmt.executeQuery();
        log.debug(sql);
        if(rs.next()){
            return true;
        }else {
            return false;
        }
    }
}
